package com.futao.excel;

import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import lombok.Getter;
import lombok.Setter;
import lombok.ToString;
import lombok.extern.slf4j.Slf4j;

import java.io.File;
import java.io.IOException;
import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.nio.file.StandardOpenOption;
import java.util.List;
import java.util.stream.Collectors;

/**
 * @author ft
 * @date 2021/6/11
 */
@Slf4j
public class Main {


    // TODO: ft 2021/6/15 改
    private static final String CATALOG_NO = "zgjj_yue_621";
    private static final String HANDLE_CLASS = "TjjYear007";


    private static final String KPI_SQL = "INSERT INTO data_kpi (" +
            "catalog_no, kpi_no, kpi_name, status, target_url, is_automatic, is_stop, stop_time, remark, pull_status, is_push)" +
            " VALUES ('" + CATALOG_NO + "', '%s', '%s', 1, '%s', 0, 0, 0, '%s', 0, 0);";
    private static final String KPI_CONFIG_SQL = "insert into data_kpi_config (kpi_no, sname, skey, svalue, stype, status)\n" +
            "values ('%s', '时间类型', 'dataTimeType', '年数据', 1, 1),\n" +
            "       ('%s', '时间格式', 'dataTimeFormart', 'yyyy', 1, 1),\n" +
            "       ('%s', '值', 'data',\n" +
            "        'TABLE//特殊处理类-tjj.year." + HANDLE_CLASS + "_fsnd&A0G0A01->{colKey:dataTime&%s,rowKey:值}', 2, 1);";


    public static void main(String[] args) {
        ImportParams params = new ImportParams();
        List<Spider> objects = ExcelImportUtil.importExcel(new File("D:\\src\\my\\simple-demo-tool\\src\\main\\java\\com\\futao\\excel\\222.xlsx"),
                Spider.class, params);

        //        List<String> strings = new ArrayList<>();
        //        objects.forEach(obj -> {
        //            String keyW3 = obj.getKeyW3();
        //            if (StringUtils.isNotBlank(keyW3)) {
        //                if (keyW3.contains("(") || keyW3.contains(")") || keyW3.contains("（") || keyW3.contains("）")) {
        //                    strings.add(obj.getZbId());
        //                }
        //            }
        //        });
        //        System.out.println(strings);

        genDelete(objects);
        // genKpiSql(objects);
        genKpiConfigSql(objects);
    }


    /**
     * 生成kpi-sql
     *
     * @param dataList
     */
    private static void genKpiSql(List<Spider> dataList) {
        StringBuilder sb = new StringBuilder();
        dataList.forEach(x -> {
            if (x.isEnable()) {
                String format = String.format(KPI_SQL, x.getZbId().trim(), x.getZbName().trim(), x.getUrl().trim(), x.getZbName().trim());
                sb.append("\r\n");
                sb.append(format);
            }
        });
        String s = sb.toString();
        try {
            Files.write(Paths.get("D:\\src\\my\\simple-demo-tool\\src\\main\\java\\com\\futao\\excel\\result.sql"), s.getBytes(StandardCharsets.UTF_8));
        } catch (IOException e) {
            e.printStackTrace();
        }
        System.out.println("kpi write success...");
    }

    /**
     * 生成kpi-sql-config
     *
     * @param dataList
     */
    private static void genKpiConfigSql(List<Spider> dataList) {
        StringBuilder sb = new StringBuilder();
        dataList.forEach(x -> {
            if (x.isEnable()) {
                // String henXiangGuanJianZi = x.getHengXiangGuanJianZi();
                String format = String.format(KPI_CONFIG_SQL, x.getZbId().trim(), x.getZbId().trim(), x.getZbId().trim(),
                        x.getZongXiangGuanJianZi());
                sb.append("\r\n");
                sb.append(format);
            }
        });
        String s = sb.toString();
        try {
            Files.write(Paths.get("D:\\src\\my\\simple-demo-tool\\src\\main\\java\\com\\futao\\excel\\result.sql"), s.getBytes(StandardCharsets.UTF_8), StandardOpenOption.APPEND);
        } catch (IOException e) {
            e.printStackTrace();
        }
        System.out.println("kpi config write success...");
    }

    private static void genDelete(List<Spider> dataList) {
        String collect = dataList.stream()
                .filter(Spider::isEnable)
                .map(Spider::getZbId)
                .collect(Collectors.joining("','"));
        collect = "delete from data_kpi_config where kpi_no in ('" + collect + "')";
        try {
            Files.write(Paths.get("D:\\src\\my\\simple-demo-tool\\src\\main\\java\\com\\futao\\excel\\delete.sql"), collect.getBytes(StandardCharsets.UTF_8));
        } catch (IOException e) {
            e.printStackTrace();
        }
    }


    @Getter
    @Setter
    @ToString
    public static class Spider {
        @Excel(name = "维护人员")
        private String whry;
        @Excel(name = "3.0指标ID")
        private String zbId;
        @Excel(name = "3.0指标名称")
        private String zbName;
        @Excel(name = "指标状态", replace = {"正常维护_true", "停止更新_false"})
        private boolean enable;
        @Excel(name = "网址")
        private String url;
        // @Excel(name = "横向关键字")
        // private String hengXiangGuanJianZi;
        @Excel(name = "纵向关键字")
        private String zongXiangGuanJianZi;
    }

}
